USE [CaseCentre]
GO

IF NOT EXISTS (
SELECT  schema_name
FROM    information_schema.schemata
WHERE   schema_name = 'CW' ) 

BEGIN
EXEC sp_executesql N'CREATE SCHEMA CW AUTHORIZATION [dbo]'
END
GO

CREATE TABLE [CW].[Controls] (
	[ID]   INT          IDENTITY (1, 1) NOT NULL,
	[Name] VARCHAR (50) NOT NULL);

CREATE TABLE [CW].[DataSource] (
	[ID]   INT         PRIMARY KEY  IDENTITY (1, 1) NOT NULL,
	[Name] VARCHAR (50) NOT NULL);

CREATE TABLE [CW].[DataValueSource] (
	[ID]           INT          PRIMARY KEY IDENTITY (1, 1) NOT NULL,
	[DataSourceID] INT          NOT NULL REFERENCES [CW].[DataSource] ([ID]),
	[Name]         VARCHAR (50) NOT NULL,
	[Value]        VARCHAR (50) NULL);

CREATE TABLE [CW].[Form] (
	[ID]   INT          PRIMARY KEY IDENTITY (1, 1) NOT NULL,
	[Name] VARCHAR (50) NOT NULL);

CREATE TABLE [CW].[FormDesign] (
	[ID]           INT          PRIMARY KEY IDENTITY (1, 1) NOT NULL,
	[FormID]       INT          NOT NULL REFERENCES [CW].[Form] ([ID]),
	[ControlType]  VARCHAR (50) NOT NULL,
	[ControlName]  VARCHAR (50) NULL,
	[LabelText]    VARCHAR (50) NULL,
	[DisplayOrder] INT          NULL,
	[DataSourceID] INT          NULL REFERENCES [CW].[DataSource] ([ID])
);

CREATE TABLE [CW].[FormConfiguration] (
	[ID]          INT          PRIMARY KEY IDENTITY (1, 1) NOT NULL,
	[Stage]       VARCHAR (50),
	[FormID]      INT   NULL REFERENCES [CW].[Form] ([ID]));

----------------------------- Case Centre ------------------------
CREATE TABLE [CW].[Country] (
	[ID]   INT         PRIMARY KEY IDENTITY (1, 1) NOT NULL,
	[Code] VARCHAR (10) NULL,
	[Name] VARCHAR (50) NULL);

CREATE TABLE [CW].[Address] (
	[ID]           INT           PRIMARY KEY IDENTITY (1, 1) NOT NULL,
	[CountryID]    INT           NOT NULL  REFERENCES [CW].[Country] ([ID]),
	[State]        VARCHAR (50)  NULL,
	[City]         VARCHAR (50)  NULL,
	[Address1]     VARCHAR (50)  NULL,
	[Address2]     VARCHAR (50)  NULL,
	[PostalCode]   VARCHAR (10)  NULL,
	[CreatedOnUtc] DATETIME2 (7) NULL);

CREATE TABLE [CW].[Business] (
	[ID]                  INT           PRIMARY KEY IDENTITY (1, 1) NOT NULL,
	[BusinessName]        VARCHAR (100) NULL,
	[Email]               VARCHAR (50)  NULL,
	[AdminComment]        VARCHAR (200) NULL,
	[CreatedOnUtc]        DATETIME2 (7) NULL,
	[LastActivityDateUtc] DATETIME2 (7) NULL,
	[PhoneNumber]         VARCHAR (20)  NULL,
	[FaxNumber]           VARCHAR (20)  NULL,
	[AddressID]           INT           NULL  REFERENCES [CW].[Address] ([ID]),
	[LeadContactId]       INT           NULL);

CREATE TABLE [CW].[BusinessOffer] (
	[ID]             INT          PRIMARY KEY IDENTITY (1, 1) NOT NULL,
	[OfferType]      INT NULL,
	[OfferRangeCode] VARCHAR (20) NULL,
	[DistressAmount] DECIMAL (10) NULL);

CREATE TABLE [CW].[ComplaintType]
(
	 ID INT PRIMARY KEY IDENTITY(1,1),
	 Code VARCHAR(50),
	 [Description] NVARCHAR(100)
);

CREATE TABLE [CW].[Consumer] (
	[ID]                  INT           PRIMARY KEY IDENTITY (1, 1) NOT NULL,
	[Title]               VARCHAR (5)   NULL,
	[FirstName]           VARCHAR (30)  NULL,
	[MiddleName]          VARCHAR (30)  NULL,
	[LastName]            VARCHAR (30)  NULL,
	[BirthDate]           DATETIME      NULL,
	[Email]               VARCHAR (50)  NULL,
	[PhoneNumber]         VARCHAR (20)  NULL,
	[FaxNumber]           VARCHAR (20)  NULL,
	[AdminComment]        VARCHAR (200) NULL,
	[CreatedOnUtc]        DATETIME      NULL,
	[LastActivityDateUtc] DATETIME2 (7) NULL,
	[AddressID]           INT           NOT NULL  REFERENCES [CW].[Address] ([ID]));

CREATE TABLE [CW].[UserProfile] (
	[ID]                INT            PRIMARY KEY IDENTITY (1, 1) NOT NULL,
	[BusinessID]        INT            NOT NULL  REFERENCES [CW].[Business] ([ID]),
	[IsBusinessAdmin]   BIT            NULL,
	[UserPosition]      VARCHAR (20)   NULL,
	[PhoneNumber]       VARCHAR (20)   NULL,
	[FaxNumber]         VARCHAR (20)   NULL,
	[Email]             VARCHAR (50)   NULL,
	[Title]             VARCHAR (10)   NULL,
	[Password]          VARCHAR (50)   NULL,
	[IsLogOn]           BIT            NULL,
	[LogOffDate]        DATETIME2 (7)  NULL,
	[LogOnAttempt]      INT            NULL,
	[FirstName]         VARCHAR (30)   NULL,
	[LastName]          VARCHAR (30)   NULL,
	[LockOutExpireDate] DATETIME2 (7)  NULL,
	[PassExpireDate]    DATETIME2 (7)  NULL,
	[AccountExpireDate] DATETIME2 (7)  NULL,
	[MachineIP]         NVARCHAR (500) NULL,
	[Deleted]           BIT            NULL);

CREATE TABLE [CW].[Product] (
	[ID]           INT           PRIMARY KEY IDENTITY (1, 1) NOT NULL,
	[Name]         VARCHAR (50)  NULL,
	[Description]  VARCHAR (100) NULL,
	[AdminComment] VARCHAR (100) NULL,
	[CreatedOnUtc] DATETIME2 (7) NULL,
	[UpdatedOnUtc] DATETIME2 (7) NULL,
	[Category]     VARCHAR (50)  NULL,
	[SubCategory]  VARCHAR (50)  NULL);

CREATE TABLE [CW].[Team] (
	[ID]                 INT           PRIMARY KEY IDENTITY (1, 1) NOT NULL,
	[Name]               VARCHAR (20)  NULL,
	[ManagerID]          INT           NOT NULL  REFERENCES [CW].[UserProfile] ([ID]),
	[ProductID]          INT           NOT NULL  REFERENCES [CW].[Product] ([ID]),
	[Description]        VARCHAR (100) NULL,
	[TeamCode]           VARCHAR (20)  NULL,
	[TeamCreationReason] VARCHAR (50)  NULL
);

CREATE TABLE [CW].[JuridictionReason] (
	[ID]          INT           PRIMARY KEY IDENTITY (1, 1) NOT NULL,
	[Name]        VARCHAR (50)  NULL,
	[Description] VARCHAR (100) NULL);

CREATE TABLE [CW].[OfferRange] (
	[ID]   INT          PRIMARY KEY IDENTITY (1, 1) NOT NULL,
	[Name] VARCHAR (50) NULL,
	[Code] VARCHAR (20) NULL);

CREATE TABLE [CW].[Pool] (
	[ID]           INT           PRIMARY KEY IDENTITY (1, 1) NOT NULL,
	[Name]         VARCHAR (20)  NULL,
	[ProductID]    INT           NOT NULL  REFERENCES [CW].[Product] ([ID]),
	[Description]  VARCHAR (50)  NULL,
	[CreatedOnUtc] DATETIME2 (7) NULL,
	[WorkPoolCode] VARCHAR (20)  NULL);

CREATE TABLE [CW].[Resolution] (
	[ID]                    INT           PRIMARY KEY IDENTITY (1, 1) NOT NULL,
	[Name]                  VARCHAR (50)  NULL,
	[ComplainID]            INT           NULL,
	[Description]           VARCHAR (50)  NULL,
	[FreeDataXML]           VARCHAR (500) NULL,
	[CreatedOnUtc]          DATETIME2 (7) NULL,
	[LastUpdatedOnUtc]      DATETIME2 (7) NULL,
	[OfferType]             INT  NULL,
	[DistressAmount]        DECIMAL (10)  NULL,
	[ResolutionRequestedBy] INT           NULL,
	[BusinessOfferID]       INT           NULL,
	[CreatedByID]           INT           NULL,
	[OfferRangeID]          INT           NOT NULL REFERENCES [CW].[OfferRange] ([ID]),
	[IsOfferInline]         BIT           NULL,
	[NavigatorAssessmentID] INT           NULL);

CREATE TABLE [CW].[Settlement] (
	[ID]               INT           PRIMARY KEY IDENTITY (1, 1) NOT NULL,
	[Name]             VARCHAR (50)  NULL,
	[ComplainID]       INT           NULL,
	[Description]      VARCHAR (50)  NULL,
	[FreeDataXML]      VARCHAR (500) NULL,
	[CreatedOnUtc]     DATETIME2 (7) NULL,
	[LastUpdatedOnUtc] DATETIME2 (7) NULL,
	[OfferType]        INT			 NOT NULL REFERENCES [CW].[OfferRange] ([ID]),
	[DistressAmount]   DECIMAL (10)  NULL,
	[DecisionReqBy]    INT           NULL,
	[BusinessOfferID]  INT           NOT NULL REFERENCES [CW].[BusinessOffer] ([ID]),
	[CreatedByID]      INT           NULL,
	[ResolutionID]     INT           NOT NULL REFERENCES [CW].[Resolution] ([ID]),
	[OfferRangeID]     INT           NULL);

CREATE TABLE [CW].[Enquiry](
	[ID] [int]			PRIMARY KEY IDENTITY(1,1) NOT NULL,
	[ConsumerID] [int]	NOT NULL REFERENCES [CW].[Consumer] ([ID]),
	[BusinessID] [int]	NOT NULL REFERENCES [CW].[Business] ([ID]),
	[ProductID] [int]	NOT NULL REFERENCES [CW].[Product] ([ID]),
	[CreatedOnUtc]		[datetime2](7) NULL,
	[LastActivityOnUtc] [datetime2](7) NULL,
	[Description]		[varchar](500) NULL,
	[AdminComment]		[varchar](100) NULL,
	[OwnerUserID]		[int] NOT NULL REFERENCES [CW].[UserProfile] ([ID]),
	[ComplaintTypeID]	[int] NOT NULL REFERENCES [CW].[ComplaintType] ([ID]));

CREATE TABLE [CW].[Complain] (
	[ID]                  INT           PRIMARY KEY IDENTITY (1, 1) NOT NULL,
	[EnquiryID]           INT           NOT NULL REFERENCES [CW].[Enquiry] ([ID]),
	[ConsumerID]          INT           NOT NULL REFERENCES [CW].[Consumer] ([ID]),
	[BusinessID]          INT           NOT NULL REFERENCES [CW].[Business] ([ID]),
	[ProductID]           INT           NOT NULL REFERENCES [CW].[Product] ([ID]),
	[CreatedOnUtc]        DATETIME2 (7) NULL,
	[LastActivityOnUtc]   DATETIME2 (7) NULL,
	[Description]         VARCHAR (500) NULL,
	[AdminComment]        VARCHAR (100) NULL,
	[Priority]            INT           NULL,
	[PoolID]              INT           NOT NULL REFERENCES [CW].[Pool] ([ID]),
	[OwnerUserID]         INT           NOT NULL REFERENCES [CW].[UserProfile] ([ID]),
	[TeamID]              INT           NOT NULL REFERENCES [CW].[Team] ([ID]),
	[ComplainRef]         INT           NULL,
	[ClosedFlag]          BIT           NULL,
	[ClosedDate]          DATETIME2 (7) NULL,
	[NonProgressFlag]     INT           NULL,
	[Stage]               INT           NULL,
	[JuridictionReasonID] INT           NOT NULL REFERENCES [CW].[JuridictionReason] ([ID]),
	[ResolutionID]        INT           NOT NULL REFERENCES [CW].[Resolution] ([ID]),
	[SettlementID]        INT           NOT NULL REFERENCES [CW].[Settlement] ([ID]),
	[ComplaintTypeID]     INT           NOT NULL REFERENCES [CW].[ComplaintType] ([ID]));

	CREATE TABLE [CW].[EnquiryCustomFormAnswer] (
	[ID]			INT				PRIMARY KEY IDENTITY (1, 1) NOT NULL,
	[EnquiryID]		INT				NOT NULL REFERENCES [CW].[Enquiry] ([ID]),
	[FormID]		INT				NULL REFERENCES [CW].[Form] ([ID]),
	[AnswerXml]		[XML]			NULL);

    CREATE TABLE [CW].[ComplainCustomFormAnswer] (
	[ID]			INT				PRIMARY KEY IDENTITY (1, 1) NOT NULL,
	[ComplainID]	INT				NOT NULL REFERENCES [CW].[Complain] ([ID]),
	[Stage]			[varchar](50)	NULL,
	[FormID]		INT				NULL REFERENCES [CW].[Form] ([ID]),
	[AnswerXml]		[XML]			NULL);